package com.ruoyi.common.utils.poi;

import com.alibaba.fastjson2.JSONObject;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.nio.charset.StandardCharsets;
import java.util.*;

public class MyExcelUtil {
    private static MyExcelUtil instance = new MyExcelUtil();

    public MyExcelUtil() {
    }

    public static MyExcelUtil getInstance() {
        return instance;
    }

    /**
     * 将 List<Map<String,Object>> 类型的数据导出为 Excel
     * 默认 Excel 文件的输出路径为 项目根目录下
     * 文件名为 filename + 时间戳 + .xlsx
     *
     * @param jsonList 数据源(通常为数据库查询数据)
     * @param filename 文件名前缀, 实际文件名后会加上日期
     * @param title    表格首行标题
     */
//    public void createExcel(HttpServletResponse response, List<Map<String, Object>> mapList, String filename, String title) {
//        // 获取数据源的 key, 用于获取列数及设置标题
//        Map<String, Object> map = mapList.get(0);
//        Set<String> stringSet = map.keySet();
//        List<String> headList = new ArrayList<>(stringSet);
//        Collections.sort(headList);  // 表头排序
//
//        // 定义一个新的工作簿
//        XSSFWorkbook wb = new XSSFWorkbook();
//        // 创建一个Sheet页
//        XSSFSheet sheet = wb.createSheet();
//
//        // 设置标题样式
//        XSSFCellStyle headWriteCellStyle = getTitleStyle(wb);
//        // 设置标题
//        setTitle(sheet, headList, headWriteCellStyle);
//
//        // 设置内容样式
//        XSSFCellStyle contentWriteCellStyle = getContentStyle(wb);
//        // 设置内容
//        setContent(sheet, headList, mapList, contentWriteCellStyle);
//
//        // 导出
//        export(response, wb, filename);
//    }

    public void createExcel(HttpServletResponse response, List<JSONObject> jsonList, String filename, String title) {
        // 获取数据源的 key, 用于获取列数及设置标题
        JSONObject object = jsonList.get(0);
        Set<String> stringSet = object.keySet();
        List<String> headList = new ArrayList<>(stringSet);
//        Collections.sort(headList);  // 表头排序

        // 定义一个新的工作簿
        XSSFWorkbook wb = new XSSFWorkbook();
        // 创建一个Sheet页
        XSSFSheet sheet = wb.createSheet();


        // 设置标题样式
        XSSFCellStyle headWriteCellStyle = getTitleStyle(wb);
        // 设置标题
        setTitle(sheet, headList, headWriteCellStyle);

        // 设置内容样式
        XSSFCellStyle contentWriteCellStyle = getContentStyle(wb);
        // 设置内容
        setContent(sheet, headList, jsonList, contentWriteCellStyle);

        // 导出
        export(response, wb, filename);
    }

    public void setTitle(XSSFSheet sheet, List<String> headList, XSSFCellStyle headWriteCellStyle) {
        // 设置标题
        XSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headList.size(); i++) {

//            sheet.autoSizeColumn(i);
            if (headList.get(i).length()<=4 && !headList.get(i).equals("时间")){
                sheet.autoSizeColumn(i);
            }else {
                sheet.setColumnWidth(i, 5000);
            }
            XSSFCell cell = row.createCell(i);

            cell.setCellValue(headList.get(i));
            cell.setCellStyle(headWriteCellStyle);
        }

    }

    public XSSFCellStyle getTitleStyle(XSSFWorkbook wb) {
        // 单元格样式类
        XSSFCellStyle headWriteCellStyle = wb.createCellStyle();
        // 设置单元格背景颜色
        headWriteCellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 128, 128)));
        headWriteCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 文字水平垂直居中
        headWriteCellStyle.setAlignment(HorizontalAlignment.CENTER);  // 水平居中
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
        // 设置字体
        XSSFFont headWriteFont = wb.createFont();
        headWriteFont.setFontName("Arial");  // 字体样式
        headWriteFont.setFontHeightInPoints((short) 10);  // 字体大小
        headWriteFont.setBold(true);  // 加粗
        headWriteFont.setColor(IndexedColors.WHITE.getIndex());
        headWriteCellStyle.setFont(headWriteFont);
        return headWriteCellStyle;
    }

//    public void setContent(XSSFSheet sheet, List<String> headList, List<Map<String, Object>> mapList, XSSFCellStyle contentWriteCellStyle) {
//        XSSFRow rows;
//        XSSFCell cells;
//        // 循环拿到的数据给所有行每一列设置对应的值
//        for (int i = 0; i < mapList.size(); i++) {
//            //在这个sheet页里创建一行
//            rows = sheet.createRow(i + 1);
//            sheet.autoSizeColumn(i + 1);
//            //给该行数据赋值
//            for (int j = 0; j < headList.size(); j++) {
//                String value = mapList.get(i).get(headList.get(j)).toString();
//                cells = rows.createCell(j);
//                cells.setCellValue(value);
//                cells.setCellStyle(contentWriteCellStyle);
//            }
//        }
//    }

    public void setContent(XSSFSheet sheet, List<String> headList, List<JSONObject> mapList, XSSFCellStyle contentWriteCellStyle) {
        XSSFRow rows;
        XSSFCell cells;
        // 循环拿到的数据给所有行每一列设置对应的值
        for (int i = 0; i < mapList.size(); i++) {
            //在这个sheet页里创建一行
            rows = sheet.createRow(i + 1);
//            sheet.autoSizeColumn(i + 1);
            //给该行数据赋值
            for (int j = 0; j < headList.size(); j++) {
                String value = mapList.get(i).get(headList.get(j)).toString();
                cells = rows.createCell(j);
                cells.setCellValue(value);
                cells.setCellStyle(contentWriteCellStyle);
            }
        }
    }

    public XSSFCellStyle getContentStyle(XSSFWorkbook wb) {
        // 单元格样式类
        XSSFCellStyle contentWriteCellStyle = wb.createCellStyle();
        // 设置单元格背景颜色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 文字水平垂直居中
        contentWriteCellStyle.setAlignment(HorizontalAlignment.CENTER);  // 水平居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
        // 设置边框
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        // 设置字体
        XSSFFont headWriteFont = wb.createFont();
        headWriteFont.setFontName("Arial");  // 字体样式
        headWriteFont.setFontHeightInPoints((short) 10);  // 字体大小
        contentWriteCellStyle.setFont(headWriteFont);
        return contentWriteCellStyle;
    }

    public void export(HttpServletResponse response, XSSFWorkbook wb, String filename) {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String encodedFilename = new String(filename.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
        response.setHeader("Content-Disposition", "attachment; filename=\"" + filename + "\"");

        try {
            wb.write(response.getOutputStream());
        } catch (Exception e) {
            System.out.printf("导出Excel异常{}", e.getMessage());
        } finally {
            IOUtils.closeQuietly(wb);
        }

    }
}
